package com.javasm.finance.dao.impl;

import com.javasm.finance.dao.ProdDao;
import com.javasm.finance.util.DBUtils;
import com.javasm.finance.entity.Product;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProdDaoImpl implements ProdDao {
    QueryRunner runner = new QueryRunner();

    @Override
    public List<Product> findQueryAll(Integer page, Integer pageSize, String prodName) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "select prod_id,prod_name,prod_reco,prod_first,online_sub,prod_isvisible,prod_reason,parent_id from fin_prod_recommend ";
        boolean isWhere = true;
        List prodList = new ArrayList();

        if (prodName != null && !"".equals(prodName)) {
            if (isWhere) {
                sql += "where ";
                isWhere = false;
            } else {
                sql += "and ";
            }
            sql += "prod_name like ? ";
            prodList.add("%" + prodName + "%");
        }
        sql += "limit " + (page - 1) * pageSize + "," + pageSize;

        BeanListHandler<Product> listHandler = new BeanListHandler<>(Product.class, new BasicRowProcessor(new GenerousBeanProcessor()));
        List<Product> productList = runner.query(conn, sql, listHandler, prodList.toArray());
        DBUtils.getClose(conn, null, null);
        return productList;
    }

    @Override
    public long findTotalNum(String prodName) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "select count(1) totalNum from fin_prod_recommend ";
        boolean isWhere = true;
        List prodList = new ArrayList();
        if (prodList != null && !"".equals(prodList)) {
            if (isWhere) {
                sql += "where ";
                isWhere = false;
            } else {
                sql += "and ";
            }
            sql += "prod_name like ? ";
            prodList.add("%" + prodName + "%");
        }
        long totalNum = runner.query(conn, sql, new ScalarHandler<>(), prodList.toArray());
        DBUtils.getClose(conn, null, null);
        return totalNum;

    }

    @Override//根据Id查询
    public Product findByProdId(Integer prodId) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "select * from fin_prod_recommend where prod_id=? ";
        BeanHandler<Product> productBeanHandler = new BeanHandler<>(Product.class, new BasicRowProcessor(new GenerousBeanProcessor()));
        Product query = runner.query(conn, sql, productBeanHandler, prodId);
        DBUtils.getClose(conn, null, null);
        return query;
    }

    @Override
    public int edit(Product product) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "update  fin_prod_recommend set prod_name=?,prod_reco=?,prod_first=?,online_sub=?, prod_isvisible=?,prod_reason=? where prod_id=?";
        int update = runner.update(conn, sql, product.getProdName(), product.getProdReco(), product.getProdFirst(), product.getOnlineSub(), product.getProdisvisible(), product.getProdReason(), product.getProdId());
        DBUtils.getClose(conn, null, null);
        return update;
    }

    @Override
    public Product finQueryProdParent(String prodName) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "select prod_name,parent_Class,parent_id from fin_prod_basic where prod_name=?";
        BeanHandler<Product> productBeanHandler = new BeanHandler<>(Product.class, new BasicRowProcessor(new GenerousBeanProcessor()));
        Product query = runner.query(conn, sql, productBeanHandler, prodName);
        return query;
    }

    @Override
    public int addProd(Product product) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "insert into fin_prod_recommend(prod_name,prod_reco,prod_first,online_sub,prod_isvisible,prod_reason,parent_class,parent_id) values(?,?,?,?,?,?,?,?) ";
        int update = runner.update(conn, sql, product.getProdName(), product.getProdReco(), product.getProdFirst(), product.getOnlineSub(), product.getProdisvisible(), product.getProdReason(), product.getParentClass(), product.getParentId());
        DBUtils.getClose(conn, null, null);
        return update;
    }

    @Override
    public List<Product> findQueryProd() throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "select a.prod_name,a.prod_id from fin_prod_basic a LEFT JOIN fin_prod_recommend b on a.prod_name = b.prod_name WHERE b.prod_name is null AND a.prod_state=0";
        BeanListHandler<Product> listHandler = new BeanListHandler<>(Product.class, new BasicRowProcessor(new GenerousBeanProcessor()));
        List<Product> query = runner.query(conn, sql, listHandler);
        DBUtils.getClose(conn, null, null);
        return query;
    }

    @Override
    public int delete(Integer prodId, Connection conn) throws SQLException {
        String sql = "delete from fin_prod_recommend where prod_id=?";
        int update = runner.update(conn, sql, prodId);
        return update;
    }

    @Override
    public List<Product> findQueryRelevance(Integer parentId,Integer prodId) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "SELECT prod_id,prod_name,relevance from fin_prod_recommend where parent_id=? and prod_id!=?";
        BeanListHandler<Product> productBeanListHandler = new BeanListHandler<>(Product.class, new BasicRowProcessor(new GenerousBeanProcessor()));
        List<Product> query = runner.query(conn, sql, productBeanListHandler,parentId, prodId);
        DBUtils.getClose(conn, null, null);
        return query;
    }

    @Override
    public int addRelevance(String relevance,Integer prodId, Connection conn) throws SQLException {
        String sql = "UPDATE fin_prod_recommend SET relevance=? where prod_id=?";
        int update = runner.update(conn, sql, relevance, prodId);
        return update;
    }


    @Override
    public Product queryRelevanceId(Integer prodId) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql="select relevance from fin_prod_recommend where prod_id=?";
        BeanHandler<Product> beanHandler = new BeanHandler<>(Product.class, new BasicRowProcessor(new GenerousBeanProcessor()));
        Product query = runner.query(conn, sql, beanHandler, prodId);
        return query;
    }
}
